package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import persistence.DBSession;

/**
 * @author: Humberto Rocha Loureiro (humbertorocha@gmail.com)
 * @modify: 
 */

public class NotesEngineDAO { 

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public List<NotesVO> listNotes(String tag) {
		List<NotesVO> result = new ArrayList<NotesVO>();
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		try {
			String sql = " select id, stock, datestock, tag, icontype, name, dateupdate, datealert " +
						 " from notes where tag like '%" + tag + "%'";

			pstat = conn.prepareStatement(sql);
		
			rs = pstat.executeQuery();
			while(rs.next()) {
				NotesVO vo = new NotesVO();
				vo.setId(rs.getLong("id"));
				vo.setStock(rs.getString("stock"));
				vo.setDateStock(rs.getString("datestock"));
				vo.setTag(rs.getString("tag"));
				vo.setIconType(rs.getString("icontype"));
				vo.setName(rs.getString("name"));
				vo.setDateUpdate(rs.getString("dateupdate"));
				vo.setDateAlert(rs.getString("datealert"));
				result.add(vo);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public List<NotesVO> listFolders() {
		List<NotesVO> result = new ArrayList<NotesVO>();
		conn = DBSession.getIstance().getConnection();
	    List<String> lsTag = new ArrayList<String>();

		PreparedStatement pstat = null;
		try {
			String sql = " select distinct tag as tags " +
						 " from notes ";

			pstat = conn.prepareStatement(sql);
		
			rs = pstat.executeQuery();
			while(rs.next()) {
				StringTokenizer token = new StringTokenizer(rs.getString("tags"),",");
				while(token.hasMoreTokens()){
					String value = token.nextToken();
					if(lsTag.indexOf(value)==-1)
						lsTag.add(value);
					
				}
			}
			for(String tag : lsTag) {
				NotesVO vo = new NotesVO();
				vo.setTag(tag);
				result.add(vo);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public NotesVO getNoteDetail(long id) {
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		NotesVO vo = new NotesVO();
		
		try {
			String sql = " select id, stock, datestock, tag, icontype, name, dateupdate, datealert, body " +
						 " from notes " + 
						 " where " +
						 "	id = ? ";
			
			pstat = conn.prepareStatement(sql);
			pstat.setLong(1, id);
		
			rs = pstat.executeQuery();
			if(rs.next()) {
				vo.setId(rs.getLong("id"));
				vo.setStock(rs.getString("stock"));
				vo.setDateStock(rs.getString("datestock"));
				vo.setTag(rs.getString("tag"));
				vo.setIconType(rs.getString("icontype"));
				vo.setName(rs.getString("name"));
				vo.setDateUpdate(rs.getString("dateupdate"));
				vo.setDateAlert(rs.getString("datealert"));
				vo.setBody(rs.getString("body"));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return vo;
	}

	
	public void update(NotesVO vo){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			String sql = " update notes set " +
						 "	 stock = ? 		" +
						 "  ,datestock = ?	" +
						 "  ,tag = ?		" +
						 "	,icontype = ?	" +
						 "  ,name = ?		" +
						 " 	,dateupdate = SYSDATE()	" +
						 " 	,datealert = ?	" +
						 " 	,body = ? 		" +
						 " where id = ? 	";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, vo.getStock());
			pstat.setString(2, vo.getDateStock());
			pstat.setString(3, vo.getTag());
			pstat.setString(4, vo.getIconType());
			pstat.setString(5, vo.getName());
			pstat.setString(6, vo.getDateAlert());
			pstat.setString(7, vo.getBody());
			pstat.setLong(4, vo.getId());
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(int id){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			
			String sql = " delete from notes where id = ? ";

			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void persist(NotesVO vo){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			
			String sql = " insert into notes (stock, datestock, tag, icontype, name, dateupdate, datealert, body) values (?,?,?,?,?,SYSDATE(),?,?)";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, vo.getStock());
			pstat.setString(2, vo.getDateStock());
			pstat.setString(3, vo.getTag());
			pstat.setString(4, vo.getIconType());
			pstat.setString(5, vo.getName());
			pstat.setString(6, vo.getDateAlert());
			pstat.setString(7, vo.getBody());
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		


}
